﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;
using IT_Tracker.ENTIDADES;

namespace IT_Tracker.DADOS
{
    public class PersistenciaBDTicket
    {
        private string conString = @"Data Source=STO-DSK-CC001\SQLEXPRESS;Initial Catalog=TicketOlosDB;Integrated Security=True; User Id=prvuser; Password=prvuser;";

        public Ticket GetTicket(int ticket, string fornecedor, string sqlcommand)
        {

            SqlConnection con = new SqlConnection(conString);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlcommand, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Ticket", ticket);
                cmd.Parameters.AddWithValue("@Fornecedor", fornecedor);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    Ticket tk = new Ticket();
                    tk.ticket = Convert.ToInt32(dr["Ticket"]);
                    tk.Descricao = dr["Descricao"].ToString();
                    tk.Operacao = dr["Operacao"].ToString();
                    tk.Chamado = Convert.ToInt32(dr["Chamado"]);
                    tk.Status = dr["Status"].ToString();
                    tk.Tipo = dr["Tipo"].ToString();
                    tk.Area = dr["Area"].ToString();
                    tk.DataAbertura = Convert.ToDateTime(dr["DataAbertura"]);
                    tk.DataPrevisao = Convert.ToDateTime(dr["DataPrevisao"]);
                    tk.DataConclusao = Convert.ToDateTime(dr["DataConclusao"]);
                    tk.Resumo = dr["Resumo"].ToString();
                    tk.Fornecedor = dr["Fornecedor"].ToString();
                    tk.Usuario = dr["Usuario"].ToString();
                    tk.DataAtualizacao = Convert.ToDateTime(dr["DataConclusao"]);

                    return tk;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public void incluirTicket(Ticket ticket, string sqlcommand)
        {
            SqlConnection con = new SqlConnection(conString);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlcommand, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Ticket", ticket.ticket);
                cmd.Parameters.AddWithValue("@Descricao", ticket.Descricao);
                cmd.Parameters.AddWithValue("@Operacao", ticket.Operacao);
                cmd.Parameters.AddWithValue("@Chamado", ticket.Chamado);
                cmd.Parameters.AddWithValue("@Status", ticket.Status);
                cmd.Parameters.AddWithValue("@Tipo", ticket.Tipo);
                cmd.Parameters.AddWithValue("@Area", ticket.Area);
                cmd.Parameters.AddWithValue("@DataAbertura", ticket.DataAbertura);                
                cmd.Parameters.AddWithValue("@Resumo", ticket.Resumo);
                cmd.Parameters.AddWithValue("@DataPrevisao", ticket.DataPrevisao);
                cmd.Parameters.AddWithValue("@DataConclusao", ticket.DataConclusao);
                cmd.Parameters.AddWithValue("@Fornecedor", ticket.Fornecedor);
                cmd.Parameters.AddWithValue("@Usuario", ticket.Usuario);
                cmd.Parameters.AddWithValue("@DataAtualizacao", ticket.DataAtualizacao);
                cmd.Parameters.AddWithValue("@CriadoPor", ticket.CriadoPor);

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;  // retorna mensagem de erro
            }
            finally
            {
                con.Close();
            }
        }



        public string deletarTicket(int ticket, string fornecedor, string sqlcommand)
        {
            SqlConnection con = new SqlConnection(conString);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlcommand, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Ticket", ticket);
                cmd.Parameters.AddWithValue("@Fornecedor", fornecedor);
                cmd.ExecuteNonQuery();
                return null; // success 
            }
            catch (Exception ex)
            {
                throw ex;  // retorna mensagem de erro
            }
            finally
            {
                con.Close();
            }
        }

        public string atualizarTicket(Ticket ticket, string sqlcommand)
        {

            SqlConnection con = new SqlConnection(conString);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(sqlcommand, con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@Ticket", ticket.ticket);
                cmd.Parameters.AddWithValue("@Descricao", ticket.Descricao);
                cmd.Parameters.AddWithValue("@Operacao", ticket.Operacao);
                cmd.Parameters.AddWithValue("@Chamado", ticket.Chamado);
                cmd.Parameters.AddWithValue("@Status", ticket.Status);
                cmd.Parameters.AddWithValue("@Tipo", ticket.Tipo);
                cmd.Parameters.AddWithValue("@Area", ticket.Area);
                cmd.Parameters.AddWithValue("@DataAbertura", ticket.DataAbertura);
                cmd.Parameters.AddWithValue("@Resumo", ticket.Resumo);
                cmd.Parameters.AddWithValue("@DataPrevisao", ticket.DataPrevisao);
                cmd.Parameters.AddWithValue("@DataConclusao", ticket.DataConclusao);
                cmd.Parameters.AddWithValue("@Fornecedor", ticket.Fornecedor);
                cmd.Parameters.AddWithValue("@Usuario", ticket.Usuario);
                cmd.Parameters.AddWithValue("@DataAtualizacao", ticket.DataAtualizacao);

                cmd.ExecuteNonQuery();
                return null; // success 
            }
            catch (Exception ex)
            {
                throw ex;  // retorna mensagem de erro
            }
            finally
            {
                con.Close();
            }

        }

    }
}
